<?php
	class Reseller{
		
		function Reseller()
		{
			
		}
		
		/**********************************************************************************\
		* Funcion encargada de obtener los datos de los resellers
		* sebastian_lara
		\***********************************************************************************/
		function getConfigurationReseller($cli_code = ''){
			
			include_once("RijndaelHex.php");
	
			$rijndael = new RijndaelHex();
			
			$sql = "SELECT cr.cli_code, cr.conf_db_host, cr.conf_db_name, cr.conf_db_user, cr.conf_db_pass, cr.conf_db_url, cr.conf_db_url, cr.conf_name_site, 
			cr.conf_email_site, cr.conf_admon FROM (SELECT cli_code FROM client c WHERE c.cli_type=2) t JOIN configuration_reseller cr ON t.cli_code=cr.cli_code ";
			
			if($cli_code != '')
				$sql .= " WHERE t.cli_code=".$cli_code;
			
			if($consult  = mysql_query($sql)) {
				$data = array();
				$i = 0;
				while($row = mysql_fetch_array($consult)){
					$data[$i]->cli_code = $row['cli_code'];
					$data[$i]->conf_db_host = $row['conf_db_host'];
					$data[$i]->conf_db_name = $row['conf_db_name'];
					$data[$i]->conf_db_user = $row['conf_db_user'];
					$data[$i]->conf_db_pass = $rijndael->lindecrypthex($row['conf_db_pass']);
					$data[$i]->conf_db_url = $row['conf_db_url'];
					$data[$i]->conf_name_site = $row['conf_name_site'];
					$data[$i]->conf_email_site = $row['conf_email_site'];
					$data[$i]->conf_admon = $row['conf_admon'];
					
					$i++;				
				}
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Funcion encargada de agregar un servicio para un reseller.
		* sebastian_lara
		\***********************************************************************************/
		public function addServices_reseller($data){
			$sql="
			INSERT INTO services (
			ser_code,
			ser_name, 
			ser_info_large,
			ser_info_short,
			ser_name_e,
			ser_info_large_e,
			ser_info_short_e,
			ser_req_country,
			ser_req_operator, 
			ser_req_models,
			ser_req_MEP,
			ser_req_id,
			ser_req_user, 
			ser_req_serie,
			ser_req_idbox,
			ser_type_code,
			ser_status,
			ser_status_masterunlock,
			ser_price,
			ser_time_response,
			ser_comment_models,
			ser_price_us,
			ser_price_bol,
			file_name,
			file_rename,
			img_name,
			img_rename,
			req_all_brand,
			ser_req_nick,
			ser_req_pass,
			ser_req_imei,
			ser_req_rm) VALUES (
			".$data->ser_code.",
			'".$data->name."',
			'".$data->info_large."',
			'".$data->info_short."',
			'".$data->name_e."',
			'".$data->info_large_e."',
			'".$data->info_short_e."',
			'".$data->req_country."',
			'".$data->req_operator."',
			'".$data->req_models."',
			'".$data->req_MEP."',
			'".$data->req_id."',
			'".$data->req_user."',
			'".$data->req_serie."',
			'".$data->req_idbox."',
			'".$data->ser_type_code."',
			'0',
			'".$data->ser_status."',
			'".$data->priceCOL."',
			'".$data->timeResponse."',
			'".$data->commentModels."',
			'".$data->priceUS."',
			'".$data->priceBOL."',
			'".$data->file_name."',
			'".$data->file_rename."',
			'".$data->img_name."',
			'".$data->img_rename."',
			'".$data->reqAllBrands."',
			'".$data->ser_req_nick."',
			'".$data->ser_req_pass."',
			'".$data->ser_req_imei."',
			'".$data->ser_req_rm."'); ";
	
			if(mysql_query($sql))
				return mysql_insert_id();
			else{
				securityMasterUnlock(62,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Esta funcion es la encargada de borrar los servicios de la base de datos
		* sebastian_lara
		\***********************************************************************************/
		public function deleteServices_reseller($code)
		{
			$sql = "DELETE FROM services WHERE services.ser_code = $code";
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}   
		}
				
		/**********************************************************************************\
		* Funcion encargada de agregar un archivo al servicio
		* sebastian_lara
		\***********************************************************************************/
		function addFileService_reseller($data)
		{
			$sql = "
			INSERT INTO service_file(
			flserv_code,
			service_code,
			flserv_name,
			flserv_rename,
			flserv_type) VALUES (
			NULL,
			'".$data->service_code."',
			'".$data->flserv_name."',
			'".$data->flserv_rename."', 
			'".$data->flserv_type."');";
	
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}    
		}
		
		/**********************************************************************************\
		* Funcion encargada de obtener los clientes del reseller
		* sebastian_lara
		\***********************************************************************************/
		function getAllClients_reseller(){
			$sql = "SELECT c.cli_code, c.cli_name, c.cli_last_name, c.cli_email FROM client c;";
			if($consult  = mysql_query($sql)){
				$data = array();
				$i = 0;
				while($row = mysql_fetch_array($consult)){
					$data[$i]->code = $row['cli_code'];
					$data[$i]->name = $row['cli_name'];
					$data[$i]->last_name = $row['cli_last_name'];
					$data[$i]->cli_email = $row['cli_email'];							
					$i++;				
				}
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			} 
		}
		
		
		/**********************************************************************************\
		* Funcion encargada de asignar los precios por defecto que tienen los servicios a un cliente
		* sebastian_lara
		\***********************************************************************************/
		function addPriceToService_reseller($data){
			$sql = "
			INSERT INTO client_price(
			clpr_code ,
			ser_code ,
			cli_code ,
			clpr_price_col ,
			clpr_price_us ,
			clpr_price_bol
			)
			VALUES (
			NULL ,
			'".$data->ser_code."', 
			'".$data->cli_code."', 
			'".$data->price_col."',
			'".$data->price_us."', 
			'".$data->price_bol."');";
			
			if(mysql_query($sql))
				return mysql_insert_id();
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			} 
		}
				
		/**********************************************************************************\
		* Funcion encargada de asociar un modelo a un servicio.
		* sebastian_lara
		\***********************************************************************************/
		function addModelToService_reseller($data){
			$sql = "INSERT INTO services_model (semo_code ,mod_code, ser_code) VALUES (NULL , '".(int)$data->mod_code."', '".(int)$data->ser_code."');";
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}    
		}
		
		
		/**********************************************************************************\
		* Funcion que permite actualizar un servicio
		* sebastian_lara
		\***********************************************************************************/
		function updateService_reseller($data){
			$sql = "
			UPDATE services SET
			ser_req_country = '".$data->req_country."',
			ser_req_operator = '".$data->req_operator."',
			ser_req_models = '".$data->req_models."',
			ser_req_MEP = '".$data->req_MEP."',
			ser_req_id = '".$data->req_id."',
			ser_req_user = '".$data->req_user."',
			ser_req_serie = '".$data->req_serie."',
			ser_req_idbox = '".$data->req_idbox."',						
			ser_time_response = '".$data->timeResponse."',			
			file_name = '".$data->file_name."',
			file_rename = '".$data->file_rename."',
			ser_status_masterunlock = '".$data->ser_status."',
			req_all_brand = '".$data->reqAllBrands."',
			ser_req_nick = '".$data->ser_req_nick."',
			ser_req_pass = '".$data->ser_req_pass."',
			ser_req_imei = '".$data->ser_req_imei."',
			ser_req_rm = '".$data->ser_req_rm."',
			img_name = '".$data->img_name."',
			img_rename = '".$data->img_rename."'
			WHERE ser_code =".(int)$data->code_service.";";			
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}    
		}
		
		/**********************************************************************************\
		* funcion encargada de obtener los precios que tienen cada servicio por cliente.
		* sebastian_lara
		\***********************************************************************************/
		function getPricesServicesClient_reseller($ser_code){
			$sql = "SELECT cp.clpr_code, cp.clpr_price_col, cp.clpr_price_us, cp.clpr_price_bol FROM client_price cp WHERE cp.ser_code=".(int)$ser_code;			
			if($consult = mysql_query($sql)){
			$data = array();
			$i=0;
			while($row = mysql_fetch_array($consult)){
				$data[$i]->clpr_code = $row["clpr_code"]; 
				$data[$i]->clpr_price_us = $row["clpr_price_us"];
				$data[$i]->clpr_price_col = $row["clpr_price_col"];
				$data[$i]->clpr_price_bol = $row["clpr_price_bol"];
				$i++;
			}			
			return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			} 
		}
				
		/**********************************************************************************\
		* funcion encargada de modificar los precios de un servicio a un cliente
		* sebastian_lara
		\***********************************************************************************/
		function setPriceService_reseller($data){
			$sql = "UPDATE client_price SET clpr_price_col = '".$data->change_col."',
			clpr_price_us = '".$data->change_us."',
			clpr_price_bol = '".$data->change_bol."' WHERE clpr_code =".(int)$data->clpr_code;
			if(mysql_query($sql))
			   return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}    
		}
		
		/**********************************************************************************\
		* funcion encargada de eliminar todos los modelos asociados a un servicio.
		* sebastian_lara
		\***********************************************************************************/
		function deleteAllModelsByService_reseller($serv_code){
			$sql = "DELETE FROM services_model WHERE ser_code = ".$serv_code.";";
			return mysql_query($sql);
		}
		
		/**********************************************************************************\
		* funcion encargada de listar los modelos asociados al un servicio
		* sebastian_lara
		\***********************************************************************************/
		function listModelByService_reseller($serv_code,$dbname)
		{	
			$data = array();	
			$sql = "
			SELECT modl.mod_code, modl.mod_name
			FROM services_model AS se_mo, model AS modl, services AS serv
			WHERE se_mo.ser_code = serv.ser_code
			AND se_mo.mod_code = modl.mod_code
			AND serv.ser_code =".$serv_code;
			
			if($consult  = mysql_query($sql)){
			$data = array();
			$i = 0;
			while($row = mysql_fetch_array($consult))
			{			
				$data[$i]    		= $row['mod_code'];				
				$i++;				
			}			
			return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			} 
		}
		
		/**********************************************************************************\
		* funcion encargada de eliminar un modelo asociado a un servicio.
		* sebastian_lara
		\***********************************************************************************/
		function deleteModelByService_reseller($mod_code,$dbname)
		{
			$sql = "DELETE FROM services_model WHERE mod_code =".$mod_code;
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			} 	
		}
		
		//brand{
			
		/**********************************************************************************\
		* Esta funcion es la encargada de agregar las marcas de telefonos que existiran en el sitio.		    
		* sebastian_lara
		\***********************************************************************************/
		public function addBrand_reseller($data){
			$sql = "INSERT INTO brand (bra_code,bra_name) VALUES (".$data->bra_code.",'".$data->bra_name."')";
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}			
		}	
		
		/**********************************************************************************\
		* Esta funcion es la encargada de modificar las marcas
		* sebastian_lara
		\***********************************************************************************/
		public function setBrand_reseller($data)
		{
			$sql ="UPDATE brand SET bra_name = '".$data->nw_name."' WHERE bra_code =".$data->bra_code;			
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Esta funcion es la encargada de borrar las marcas de la base de datos
		* sebastian_lara
		\***********************************************************************************/
		public function deleteBrand_reseller($code)
		{
			$sql = "DELETE FROM brand WHERE bra_code=".$code;
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
			
		//brand}	
		
		//model{
			
		/**********************************************************************************\
		* Esta funcion es la encargada de agregar los modelos de telefonos que existiran en el sitio.
		* sebastian_lara
		\***********************************************************************************/
		public function addModel_reseller($data){
			$sql = "INSERT INTO model (mod_code, bra_code, mod_name) VALUES (".$data->mod_code.",'".$data->bra_code."', '".$data->mod_name."')";		
			if(mysql_query($sql))
			    return mysql_insert_id();
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Esta funcion es la encargada de modificar los modelos de telefonos que existiran en el sitio.
		* sebastian_lara
		\***********************************************************************************/	
		public function setModel_reseller($data)
		{
			$sql ="UPDATE model SET mod_name='".$data->model."',bra_code='".$data->brand."' WHERE mod_code =".$data->mod_code;			
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			};		
		}
		
		/**********************************************************************************\
		* Esta funcion es la encargada de borrar los modelos de la base de datos.
		* sebastian_lara
		\***********************************************************************************/	
		public function deleteModel_reseller($code)
		{
			$sql = "DELETE FROM model WHERE mod_code = ".$code;
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		//model}
		
		//operator{
		
		/**********************************************************************************\
		* Funcion encargada de agregar un nuevo operador
		* sebastian_lara
		\***********************************************************************************/
		public function addOperator_reseller($data)
		{	
			$sql = "
			INSERT INTO red (red_id, cou_code, red_name) 
			VALUES (".$data->red_id.", '".$data->cou_code."', '".$data->opr_name."');";		
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}		
		}	
				
		/**********************************************************************************\
		* Funcion encargada de editar un operador
		* sebastian_lara
		\***********************************************************************************/
		function updateOperator_reseller($data)
		{
			$sql = "
			UPDATE  red SET  red_name =  '".$data->opr_name."',
			cou_code =  '".$data->cou_code."' WHERE  red_id =".$data->opr_code;
			
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* funcion encargada de eliminar un operador
		* sebastian_lara
		\***********************************************************************************/
		function deleteOperator_reseller($code_operator)
		{
			$sql = "DELETE FROM red WHERE red_id =".$code_operator;		
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}	
		}
			
		//operator}	
		
		// grupo{
				
		/**********************************************************************************\
		* funcion encargada de agregar un nuevo tipo de servicio.
		* sebastian_lara
		\***********************************************************************************/
		function newServiceType_reseller($data){
			$sql = "
			INSERT INTO service_type(
			ser_type_code ,
			ser_type_name ,
			ser_type_description ,
			type_service,
			ser_type_name_e,
			ser_type_description_e
			)
			VALUES (
			NULL, 
			'".$data->ser_type_name."',
			'".$data->ser_type_description."',
			'".$data->type_service."',
			'".$data->ser_type_name_e."',
			'".$data->ser_type_description_e."');";
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
			
		
		/**********************************************************************************\
		* funcion encargada de actualizar los datos de un tipo de servicio.
		* sebastian_lara
		\***********************************************************************************/
		function updateServiceType_reseller($data)
		{		
			$sql = "
			UPDATE service_type SET 
			type_service = '".$data->type_service."'			
			 WHERE ser_type_code =".$data->code_type_serv;
			
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* funcion encargada de borrar un tipo de servicio.
		* sebastian_lara
		\***********************************************************************************/
		function deleteServiceType_reseller($code_type_service)
		{
			$sql= "DELETE FROM service_type WHERE ser_type_code = ".$code_type_service;
			if(mysql_query($sql))
			    return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		//grupo}	
		
		//liberacion

		/**********************************************************************************\
		* Funcion responsable de verificar si el imei es de un cliente de  un reseller.
		* sebastian_lara
		\***********************************************************************************/
		public function imeiIsClientReseller($libimei_code){
			$sql = " SELECT count(lr.libimeir_code_imei) as cant FROM liberation_imei_reseller lr WHERE lr.libimei_code=".$libimei_code;			
			$row = mysql_fetch_array(mysql_query($sql));
			return $row['cant'];
		}
		
		/**********************************************************************************\
		* Esta funcion se encarga de obtener los datos necesarios para hacer la liberacion en en reseller.
		* sebastian_lara
		\***********************************************************************************/
		public function getDataImeiMaster($libimei_code){
			$sql  = " SELECT lr.libimeir_code_imei, lr.libimeir_code_cliente_reseller, lr.reseller_code, ";
			$sql .= " cli.cli_code, cli.cli_language_email,cli.cli_name,cli.cli_last_name , cli.cli_email ";
			$sql .= " FROM liberation_imei_reseller lr JOIN client cli ON lr.reseller_code = cli.cli_code ";
			$sql .= " WHERE lr.libimei_code=".$libimei_code.";";
			if($consulta = mysql_query($sql)){
				$row = mysql_fetch_array($consulta);
	
				$data->libimeir_code_imei 				= $row['libimeir_code_imei'];//cod del imei en la bd del reseller
				$data->libimeir_code_cliente_reseller 	= $row['libimeir_code_cliente_reseller'];//cod del cleinte en la bd del reseller.
				$data->reseller_code 					= $row['reseller_code'];//cod del reseller.
				
				//datos del reseller
				$data->cli_code				= $row["cli_code"];
				$data->cli_language_email	= $row["cli_language_email"];
				$data->cli_name 			= $row["cli_name"];
				$data->cli_last_name 		= $row["cli_last_name"];
				$data->cli_email 			= $row["cli_email"];
				
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Esta funcion se encarga de obtener los datos del clientede un reseller.
		* sebastian_lara
		\***********************************************************************************/
		public function getDataClient_reseller($cli_code)
		{
			$sql  = " SELECT cli.cli_code, cli.cli_name, cli.cli_last_name, cli.cli_language_email, cli.cli_email, cli.cli_bonus, cli.cli_payment_form ";
			$sql .= " FROM client cli WHERE cli.cli_code=".$cli_code;
						
			if($consulta = mysql_query($sql)){
			$row = mysql_fetch_array($consulta);
			
			//datos del cliente en reseller
			$data->cli_name 			= $row["cli_name"];
			$data->cli_last_name 		= $row["cli_last_name"];
			$data->cli_language_email	= $row["cli_language_email"];	
			$data->cli_email 			= $row["cli_email"];
			$data->cli_code				= $row["cli_code"];			
			
			return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		

		/**********************************************************************************\
		* esta funcion inserta el codigo de liberacion de un imei para el cliente de un reseller
		* sebastian_lara
		\***********************************************************************************/
		function insertCodeLiberation_reseller($data){
			$sql="
			UPDATE liberations_imei SET 
			libimei_number_liberation = '".$data->number_lib."',
			libimei_date_liberation = '".date("Y-m-d H:i:s")."',
			libimei_text_cable = '".$data->comment."',
			libimei_status = '1' 
			WHERE libimei_code =".$data->code_imei.";";		
			
			if(mysql_query($sql))
				return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}  
		}
		
		/**********************************************************************************\
		* funcion encargada de cambiar el estado de un imei
		* sebastian_lara
		\***********************************************************************************/
		function changeStatusImei_reseller($code_imei,$status)
		{
			$sql = "UPDATE liberations_imei SET libimei_status = '".$status."', libimei_date_liberation = '".date("Y-m-d H:i:s")."' WHERE libimei_code =".$code_imei;
			if(mysql_query($sql))
			  return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Esta funcion es la encargada de obtener los datos de una liberacion , imei cable o archivo
		* sebastian_lara
		\***********************************************************************************/
		function getDataImei_reseller($code_imei){
			$sql = "
			SELECT * FROM liberations_imei AS libImei, liberations AS lib , client AS cli , services AS serv, service_type AS sert
			WHERE lib.lib_code = libImei.lib_code
			AND lib.cli_code = cli.cli_code
			AND lib.ser_code = serv.ser_code
			AND serv.ser_type_code = sert.ser_type_code
			AND libimei_code =".$code_imei;
					
			if($consult  = mysql_query($sql)){
			
				$row = mysql_fetch_array($consult);
				
				$data->code    					 = $row['libimei_code'];
				$data->imei_status	 			 = $row['libimei_status'];//0:no liberado 1:liberado 2:se trato de liberar pero no se pudo.3: Not In Data Base.		
				$data->libimei_number			 = $row['libimei_number'];	
				$data->libimei_date				 = $row['libimei_date'];
				$data->libimei_date_liberation	 = $row['libimei_date_liberation'];//Fecha de liberacion de cada imei o fecha en que fue modificado
				$data->libimei_number_liberation = $row['libimei_number_liberation'];//Numero de liberacion por cada imei	
				$data->libimei_bonus 			 = $row['libimei_bonus'];//Creditos que consumio la liberacion.
				$data->libimei_money 			 = $row['libimei_money'];//COL: moneda colombiana USD: Dolar BOL: Moneda de venezuela
				
				$data->cli_code    		 	 = $row['cli_code'];
				$data->cli_name    		 	 = $row['cli_name'];
				$data->cli_last_name    	 = $row['cli_last_name'];
				$data->cli_email    		 = $row['cli_email'];		
				
				$data->cli_payment_form			= $row['cli_payment_form'];
				$data->cli_rename				= $row['cli_rename'];
				$data->cli_status				= $row['cli_status'];
				
				$data->ser_name    		 		= $row['ser_name'];					
				$data->ser_img_rename			= $row['img_rename'];	
								
				$data->type_service				= $row['type_service'];				
				$data->lib_description			= $row['lib_description'];
									 
				//archivo
				$data->libimei_original 	 	= $row['libimei_original'];
				$data->libimei_renombre  	 	= $row['libimei_renombre'];
				
				$data->lib_model				= $row['lib_model'];		
				$data->lib_operator				= $row['lib_operator'];
				$data->lib_numMep				= $row['lib_numMep'];				
				$data->lib_id					= $row['lib_id'];
				$data->lib_serie				= $row['lib_serie'];
				$data->lib_user					= $row['lib_user'];
				$data->lib_id_box				= $row['lib_id_box'];
												
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
			
		//liberacion
		
		/**********************************************************************************\
		* Esta funcion es la encargada de reembolsar los creditos de un imei que no se encontro en la BD
		* sebastian_lara
		\***********************************************************************************/
		public function repayBonus_reseller($data)
		{
			$sql = "INSERT INTO bonus_client (
			bocl_code ,
			cli_code ,
			bocl_date ,
			bocl_lot ,
			bocl_method_of_payment ,
			bocl_money ,
			bocl_type ,
			bocl_comment ,
			bocl_currency ,
			bocl_references ,
			bocl_status ,
			bocl_name ,
			bocl_last_name ,
			bocl_bank ,
			bocl_who ,
			bocl_mtcn ,
			bocl_admincomment ,
			country_name ,
			city_name ,
			payment_to ,
			email_used_for_payment ,
			file_name ,
			file_rename,
			lib_code
			)
			VALUES (
			NULL , '".$data->cli_code."', '".$data->datet."', '".$data->lot."', NULL , '', '4', '', NULL , '', '".$data->status."', '', '', '', '".$data->who."', '', '', '', '', '', '', '', '',".$data->libImei_code.");";
			if(mysql_query($sql))		
				return mysql_insert_id();		
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**********************************************************************************\
		* Suma bonus al cliente, de la tabla clientes
		* sebastian_lara
		\***********************************************************************************/
		public function addBonusClient_reseller($idClient,$bonus)
		{
			if(isset($idClient) && $idClient != '' && isset($bonus) && $bonus!= '')
			{
				$sql = "UPDATE client SET cli_bonus = cli_bonus+".$bonus." WHERE cli_code = ".$idClient;
				if(mysql_query($sql))
					return true;
				else{
				securityMasterUnlock(63,1,$sql);
				return false;
				}	
			}		
			return false;
		}
		
		// Envio de correo global para la aplicacion por parte de reseller.
		// @param $body --> Cuerpo que es la parte dinamica de la aplicaci�n que debe ser un table y este internamente cambia de acuerdo al mensaje.
		// @param $title --> Titulo en la franja negra con que se envia el mensaje.
		// 
		
		public function sendEmailSite_reseller($body){
			$sql = "SELECT conf_signature FROM configuration;";
			$consult = mysql_query($sql);
			$row = mysql_fetch_array($consult);
			$signature =  $row['conf_signature'];
			
			return '<div>'.$body.'</div><div '.__style_signatureMU__.'>'.nl2br($signature).'</div>';
		}
		
		/**
		*	Funcion responsable de obtener los datos de un usuarios en el reseller.
		*	@autor: jsebastián lara <jsebastian@emquisoft.com>
		*	@date: 26 de julio de 2011
		*/		
		function getUserData_reseller($use_code){
			$sql = "SELECT u.use_code, u.use_email, u.use_password FROM user u WHERE u.use_code = ".$use_code;
			if($result = mysql_query($sql)){			
			$row = mysql_fetch_array($result);
			
			$data->use_code 	= $row['use_code'];
			$data->use_email   	= $row['use_email'];
			$data->use_password = $row['use_password'];
			
			return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		/**
		*	Funcion encargada de listar datos de los resellers.
		*	@autor: JSL
		*	@date: 17 NOV 2011
		*/
		function listResellers($order_by = ''){
			$sql = "SELECT r.conf_db_url, r.conf_name_site, c.cli_code, c.cli_name, c.cli_last_name, c.cli_email, c.cli_payment_form FROM configuration_reseller r JOIN client c ON r.cli_code=c.cli_code ".$order_by.";";
			if($result = mysql_query($sql)){
				$data;
				$i = 0;
				while($row = mysql_fetch_array($result)){
					$data[$i]->conf_db_url = $row["conf_db_url"];
					$data[$i]->conf_name_site = $row["conf_name_site"];
					$data[$i]->cli_code = $row["cli_code"];
					$data[$i]->cli_name = $row["cli_name"];
					$data[$i]->cli_last_name = $row["cli_last_name"];
					$data[$i]->cli_email = $row["cli_email"];
					$data[$i]->cli_payment_form = $row["cli_payment_form"];
					$i++;
				}
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Funcion encargada de contar cuantos resellers existen en Master-UNlock
		*	@autor: JSL
		*	@date: 17 NOV 2011
		*/
		function countResellers(){
			$sql = "SELECT COUNT(*) AS cant FROM configuration_reseller r JOIN client c ON r.cli_code=c.cli_code;";
			if($result = mysql_query($sql)){
				$row = mysql_fetch_array($result);
				return $row["cant"];
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
	
		//-------------
		/**
		*	Esta funcion obtiene los tipos de servicios que estan en master-unlock pero que no estan en el reseller.
		*	@autor: JSL
		*	@date: 17 NOV 2011
		*/
		function getServicesTypeToInsert_reseller($mydata){
			$sql = "SELECT s1.ser_type_code, s1.ser_type_name, s1.ser_type_description, s1.type_service, s1.ser_type_name_e, s1.ser_type_description_e FROM ".$mydata->db_masterunlock.".service_type s1 LEFT JOIN ".$mydata->db_reseller.".service_type s2 ON s1.ser_type_code=s2.ser_type_code WHERE s2.ser_type_code IS NULL;";
			if($result = mysql_query($sql)){			
			$data = array();
			$i=0;
			while($row = mysql_fetch_array($result)){
				$data[$i]->ser_type_code = $row["ser_type_code"];
				$data[$i]->ser_type_name = mysql_real_escape_string($row["ser_type_name"]);
				$data[$i]->ser_type_description = mysql_real_escape_string($row["ser_type_description"]);
				$data[$i]->type_service = $row["type_service"];
				$data[$i]->ser_type_name_e = mysql_real_escape_string($row["ser_type_name_e"]);
				$data[$i]->ser_type_description_e = mysql_real_escape_string($row["ser_type_description_e"]);
				$i++;
			}			
			return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Esta funcion obtiene los servicios que estan en master-unlock pero que no estan en el reseller.
		*	@autor: JSL
		*	@date: 18 NOV 2011
		*/
		function getServicesToInsert_reseller($mydata){
			$sql = "SELECT s1.ser_code, s1.ser_name, s1.ser_info_large, s1.ser_info_short, s1.ser_name_e, s1.ser_info_large_e, s1.ser_info_short_e, s1.ser_req_country,
			 s1.ser_req_operator, s1.ser_req_models, s1.ser_req_MEP, s1.ser_req_id, s1.ser_req_user, s1.ser_req_serie, s1.ser_req_idbox, s1.ser_type_code, s1.ser_status,
			 s1.ser_price, s1.ser_time_response, s1.ser_comment_models, s1.ser_price_us, s1.ser_price_bol, s1.file_name, s1.file_rename, s1.img_name, s1.img_rename,
			 s1.req_all_brand, s1.ser_req_nick, s1.ser_req_pass, s1.ser_req_imei, s1.ser_req_rm FROM ".$mydata->db_masterunlock.".services s1 
			 LEFT JOIN ".$mydata->db_reseller.".services s2 ON s1.ser_code=s2.ser_code WHERE s2.ser_code IS NULL;";
			if($result = mysql_query($sql)){
				$data = array();
				$i=0;
				while($row = mysql_fetch_array($result)){				
					$data[$i]->ser_code = $row["ser_code"];
					$data[$i]->name = mysql_real_escape_string($row["ser_name"]);
					$data[$i]->info_large = mysql_real_escape_string($row["ser_info_large"]);
					$data[$i]->info_short = mysql_real_escape_string($row["ser_info_short"]);
					$data[$i]->name_e = mysql_real_escape_string($row["ser_name_e"]);
					$data[$i]->info_large_e = mysql_real_escape_string($row["ser_info_large_e"]);
					$data[$i]->info_short_e = mysql_real_escape_string($row["ser_info_short_e"]);
					$data[$i]->req_country = $row["ser_req_country"];
					$data[$i]->req_operator = $row["ser_req_operator"];
					$data[$i]->req_models = $row["ser_req_models"];				
					$data[$i]->req_MEP = $row["ser_req_MEP"];
					$data[$i]->req_id = $row["ser_req_id"];
					$data[$i]->req_user = $row["ser_req_user"];
					$data[$i]->req_serie = $row["ser_req_serie"];
					$data[$i]->req_idbox = $row["ser_req_idbox"];
					$data[$i]->ser_type_code = $row["ser_type_code"];				
					$data[$i]->ser_status = $row["ser_status"];				
					$data[$i]->priceCOL = $row["ser_price"];
					$data[$i]->timeResponse = mysql_real_escape_string($row["ser_time_response"]);
					$data[$i]->commentModels = mysql_real_escape_string($row["ser_comment_models"]);
					$data[$i]->priceUS = $row["ser_price_us"];
					$data[$i]->priceBOL = $row["ser_price_bol"];
					$data[$i]->file_name = $row["file_name"];
					$data[$i]->file_rename = $row["file_rename"];
					$data[$i]->img_name = $row["img_name"];
					$data[$i]->img_rename = $row["img_rename"];
					$data[$i]->reqAllBrands = $row["req_all_brand"];
					$data[$i]->ser_req_nick = $row["ser_req_nick"];
					$data[$i]->ser_req_pass = $row["ser_req_pass"];
					$data[$i]->ser_req_imei = $row["ser_req_imei"];
					$data[$i]->ser_req_rm = $row["ser_req_rm"];
					
					if($data[$i]->req_models == '1' || $data[$i]->req_models == '2')
						$data[$i]->listModelServices = $this->listModelServices($data[$i]->ser_code);
					
					$i++;
				}
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Funcion encargada de listar los modelos asociados a un servicio.
		*	@sutor : JSL
		*	@date : 18 NOV 2011
		*/		
		function listModelServices($ser_code){
			$sql = "SELECT sm.mod_code FROM services_model sm WHERE sm.ser_code=".(int)$ser_code.";";
			if($result = mysql_query($sql)){
				$data = array();
				$i=0;			
				while($row = mysql_fetch_array($result)){
					$data[$i]->mod_code = $row["mod_code"];
					$data[$i]->ser_code = $ser_code;
					$i++;	
				}
				return $data;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Funcion encargada validar si un modelo esta relacionado con un servicio.
		*	@sutor : JSL
		*	@date : 18 NOV 2011
		*/		
		function issetModelService($data){
			$sql = "SELECT COUNT(*) AS cant FROM services_model sm WHERE sm.ser_code=".$data->ser_code." AND sm.mod_code=".$data->mod_code.";";
			if($result = mysql_query($sql)){
				$row = mysql_fetch_array($result);
				if($row["cant"]>0)
					return true;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}		
		
		/**
		*	Funcion encargada de verificar si existe o no el precio de un servicio para un cliente.
		*	@autor: JSL
		*	@date: 18 NOV 2011
		*/
		function issetPriceClientService($data){
			$sql = "SELECT COUNT(*) AS cant FROM client_price cp WHERE cp.ser_code = ".(int)$data->ser_code." AND cp.cli_code=".(int)$data->cli_code.";";
			if($result = mysql_query($sql)){
				$row = mysql_fetch_array($result);
				if($row["cant"]>0)
					return true;
				return false;
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Función encargada de obtener los servicios que no coinciden en su tipo de servicio.
		*	@autor: JSL
		*	date: 22 NOV 2011
		*/
		function listServicesToSync($mydata){
			$sql = "
			SELECT s1.ser_code, s1.ser_type_code, s1.ser_price, s1.ser_price_bol, s1.ser_price_us, s2.ser_price AS ser_price_rs, s2.ser_price_bol AS ser_price_bol_rs,
			s2.ser_price_us AS ser_price_us_rs,(s1.ser_price<>s2.ser_price) AS change_col, (s1.ser_price_bol<>s2.ser_price_bol) AS change_bol,
			(s1.ser_price_us<>s2.ser_price_us) AS change_us 
			FROM ".$mydata->db_masterunlock.".services s1 
			JOIN ".$mydata->db_reseller.".services s2 ON s1.ser_code=s2.ser_code 
			WHERE s1.ser_price<>s2.ser_price OR s1.ser_price_bol<>s2.ser_price_bol OR s1.ser_price_us<>s2.ser_price_us;";
			//WHERE s1.ser_type_code<>s2.ser_type_code OR s1.ser_price<>s2.ser_price OR s1.ser_price_bol<>s2.ser_price_bol OR s1.ser_price_us<>s2.ser_price_us;
			if($result = mysql_query($sql)){
				$data = array();
				$i=0;
				while($row = mysql_fetch_array($result)){
					$data[$i]->ser_code = $row["ser_code"];
					$data[$i]->ser_type_code = $row["ser_type_code"];
					$data[$i]->ser_price = $row["ser_price"];
					$data[$i]->ser_price_bol = $row["ser_price_bol"];
					$data[$i]->ser_price_us = $row["ser_price_us"];
					
					$data[$i]->ser_price_rs = $row["ser_price_rs"];
					$data[$i]->ser_price_bol_rs = $row["ser_price_bol_rs"];
					$data[$i]->ser_price_us_rs = $row["ser_price_us_rs"];
					//$data[$i]->change_group = $row["change_group"];
					$data[$i]->change_col = $row["change_col"];
					$data[$i]->change_bol = $row["change_bol"];
					$data[$i]->change_us = $row["change_us"];
					
					$i++;
				}
				return $data;
			}else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}	
		}
		
		/**
		*	Función encargada de actualizar el grupo para un servicio
		*	@autor: JSL
		*	date: 22 NOV 2011
		*/
		function syncService($data){
			$sql = 
			"UPDATE services SET 
			ser_price=".$data->ser_price.", 
			ser_price_bol=".$data->ser_price_bol.",  
			ser_price_us=".$data->ser_price_us."
			WHERE ser_code=".(int)$data->ser_code.";";
			if(mysql_query($sql))
			  return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}  
		}
		
		/**
		*	Funcion encargada de editar informacion especifica de un sevicio
		*	@autor_ JSL
		*	@date: 23 NOV 2011
		*/
		function setDataService_reseller($data,$action){
			
			$set = "";
			switch ( $action ) {
				case 1://tiempo de respuesta
					$set = " ser_time_response='".$data->ser_time_response."' ";
				break;
				
				case 2://precio de compra
					$set = " ser_price_purchase=".$data->ser_price_purchase.", ser_price=".$data->new_price_col." ,ser_price_us=".$data->new_price_us.",ser_price_bol=".$data->new_price_bol;
				break;
				
				case 3://estado
					$set = " ser_status='".$data->ser_status."' ";
				break;
				
				default:
					return false;
				break;			
			}
				
			$sql = "UPDATE  services SET ".$set." WHERE ser_code=".(int)$data->ser_code."";
			if(mysql_query($sql))
			  return true;
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Funcion responsable de obtener el coste de una liberacion de un cliente de reseller
		*	@autor: JSL
		*	@date: 22 DIC 2011
		*	@parameter: $libimei_code: codigo de la liberacion en la BD del reseller
		*/
		function getCreditUsedUnlock_reseller($libimei_code){
			$sql = "SELECT li.libimei_bonus FROM liberations_imei li WHERE li.libimei_code=".$libimei_code.";";
			if($result = mysql_query($sql)){
				$row = mysql_fetch_array($result);
				return $row["libimei_bonus"];
			}
			else{
				securityMasterUnlock(63,1,$sql);
				return false;
			}
		}
		
		/**
		*	Funcion responsable de insertar un nuevo pais en un reseller
		*	@autor: JSL
		*	@date: 22/02/2012
		*/
		function insertCountry_reseller($data){
			$sql = "INSERT INTO country ( cou_code, cou_name) VALUES ( ".$data->cou_code.", '".$data->cou_name."' );";
					
			if ( mysql_query ($sql) ){
				return mysql_insert_id ();
			}
			else
				return false;
		}
		
		/**
		*	Función responsable insertar un nuevo operador en un reseller
		*	@autor: JSL
		*	@date: 22/02/2012
		*/
		function insertNetwork_reseller($data){
			$sql = "INSERT INTO red ( red_id, cou_code, red_name) VALUES ( ".$data->red_id.", ".$data->cou_code.", '".$data->red_name."' );";
					
			if ( mysql_query ($sql) ){
				return mysql_insert_id ();
			}
			else
				return false;
		}
	}
?>